<?php

App::uses('AppController', 'Controller');

/**
 * OrdenesTags Controller
 *
 * @property OrdenesTag $OrdenesTag
 */
class PublicosController extends AppController
{

    public $layout = 'reportes';
    public $uses = array(
        'Cambiordene',
        'Ordene',
        'OrdenesTag',
        'DocumentosOrdene',
        'Plano',
        'CambiordenesTag',
        'Contrato',
        'PlanosTag',
        'Tag',
        'Cambiocontrato',
        'Factura'
        );
    public $components = array('Fecha');
    
    public function index()
    {
        $reportes = $this->Reporte->find('all');
        $this->set(compact('reportes'));
    }

    public function listadotags()
    {

        $ordenes = $this->Ordene->find('all', array('recursive' => 0));
        $this->set(compact('ordenes'));
    }

    public function detalletags($idOrden = null)
    {
        //debug($idOrden);
        $sqlOrdenesTags = "SELECT ot.id, ot.ordene_id, ot.tag_id, ot.monto, tags.descripcion1
                            FROM ordenes_tags ot
                            LEFT JOIN tags ON ot.tag_id = tags.id
                            WHERE ot.ordene_id = $idOrden";
        $datosOrden = $this->Ordene->find('first', array('recursive' => -1, 'conditions' =>
                array('id' => $idOrden)));

        $ordenesTags = $this->OrdenesTag->query($sqlOrdenesTags);
        $this->set(compact('ordenesTags', 'idOrden', 'datosOrden'));
    }
    public function reporte1()
    {

        $sqlCambirdenes = "SELECT ordenes.nombre, co.numero, co.volumen, co.monto, co.original, co.copia, co.original
                            FROM cambiordenes co
                            LEFT JOIN ordenes ON co.ordene_id = ordenes.id
                            WHERE co.volumen = 0
                            OR co.monto = 0";

        $cambioOrdenes = $this->Cambiordene->query($sqlCambirdenes);

        $this->set(compact('cambioOrdenes'));
    }

    public function duplicados()
    {
        $sqlDuplicados = "SELECT COUNT(nombre) as cant , nombre
                            FROM ordenes
                            GROUP BY nombre";
        $ordeneDuplicados = $this->Ordene->query($sqlDuplicados);
        //debug($ordeneDuplicados);
        $this->set(compact('ordeneDuplicados'));
    }

    public function podesigual()
    {
        $sqldes = "SELECT SUM(monto) as total , ordene_id
                    FROM ordenes_tags
                    GROUP BY ordene_id";
        $desiguales = $this->OrdenesTag->query($sqldes);
        $arrayOrdenes = array();
        $c = 0;
        foreach ($desiguales as $d)
        {
            $idOrden = $d['ordenes_tags']['ordene_id'];
            $montoSumaTag = $d['0']['total'];
            $busOrden = $this->Ordene->find('first', array('recursive' => -1, 'conditions' =>
                    array('id' => $idOrden)));
            $montoOrden = $busOrden['Ordene']['monto'];
            if ($montoSumaTag != $montoOrden)
            {
                $arrayOrdenes[$c] = $idOrden;
                $c++;
            }
            //debug($arrayOrdenes);
            //if($b)

        }
        //debug($arrayOrdenes);
        $ordenesDes = $this->Ordene->find('all', array('recursive' => -1, 'conditions' =>
                array('id' => $arrayOrdenes)));

        $this->set(compact('ordenesDes'));
        //debug($desiguales);
        //debug($ordenesDes);
    }

    public function codesigual()
    {
        $sqldes = "SELECT SUM(monto) as total , cambiordene_id
                    FROM cambiordenes_tags
                    GROUP BY cambiordene_id";
        $desiguales = $this->Cambiordene->query($sqldes);
        $arrayCambios = array();
        $c = 0;
        // debug($desiguales);exit;
        foreach ($desiguales as $d)
        {
            $idCambio = $d['cambiordenes_tags']['cambiordene_id'];
            $montoSumaTag = $d['0']['total'];
            $busOrden = $this->Cambiordene->find('first', array('recursive' => -1,
                    'conditions' => array('id' => $idCambio)));
            $montoOrden = $busOrden['Cambiordene']['monto'];
            if ($montoSumaTag != $montoOrden)
            {
                $arrayCambios[$c] = $idCambio;
                $c++;
            }
            //debug($busOrden);
            //if($b)

        }
        // debug($arrayCambios);
        $cambiosDes = $this->Cambiordene->find('all', array('recursive' => 0,
                'conditions' => array('id' => $arrayCambios)));
        // debug($cambiosDes);
        $this->set(compact('cambiosDes'));
        //debug($desiguales);
        //debug($ordenesDes);
    }

    public function sinpo()
    {
        $po = $this->Ordene->find('all', array('recursive' => 0, 'conditions' => array('Ordene.monto !=' =>
                    0, )));
        //debug($po);
        $c = 0;
        $cantsinpo = array();
        foreach ($po as $p)
        {
            $idOrden = $p['Ordene']['id'];
            $docpo = $this->DocumentosOrdene->find('first', array('recursive' => -1,
                    'conditions' => array('ordene_id' => $idOrden, 'documento_id' => 19)));
            if (empty($docpo))
            {
                $cantsinpo[$c] = $idOrden;
                $c++;
            }

        }
        //debug($cantsinpo);
        $povol = $this->Ordene->find('all', array('recursive' => -1, 'conditions' =>
                array('id' => $cantsinpo, 'volumenes' => 1)));

        //debug($povol);exit;
        foreach ($povol as $pv)
        {
            $codOrden = $pv['Ordene']['id'];
            $this->request->data['DocumentosOrdene']['ordene_id'] = $codOrden;
            $this->request->data['DocumentosOrdene']['volumen'] = 1;
            $this->request->data['DocumentosOrdene']['documento_id'] = 19;
            $this->request->data['DocumentosOrdene']['original'] = 0;
            $this->DocumentosOrdene->create();
            if ($this->DocumentosOrdene->save($this->request->data))
            {
                echo 'guardo ' . $codOrden;
            }
            //debug($this->request->data);
        }
        //debug($povol);exit;
    }

    public function cotagsnocuadra()
    {
        $cambiOrdene = $this->Cambiordene->find('all', array('recursive' => -1, 'order' =>
                array('ordene_id ASC', 'numero ASC')));

        //debug($cambioOrdene);
        $co = 0;
        $arrayCo = array();
        foreach ($cambiOrdene as $co)
        {
            $idCo = $co['Cambiordene']['id'];
            $nCo = $co['Cambiordene']['numero'];
            $montoCo = $co['Cambiordene']['monto'];

            $sqlSumTag = "SELECT SUM(MONTO) as monto, cambiordene_id FROM cambiordenes_tags WHERE cambiordene_id = $idCo";
            $sumTag = $this->CambiordenesTag->query($sqlSumTag);
            //debug($sumTag); exit;
            if ($nCo == 1)
            {
                $resta = $montoCo - $sumTag;
            }
            //$sumtag =
        }
        //debug($ordenes);
    }

    public function posinpo()
    {
        $po = $this->Ordene->find('all', array('recursive' => 0, 'conditions' => array('Ordene.monto !=' =>
                    0, )));
        //debug($po);
        $c = 0;
        $cantsinpo = array();
        foreach ($po as $p)
        {
            $idOrden = $p['Ordene']['id'];
            $docpo = $this->DocumentosOrdene->find('first', array('recursive' => -1,
                    'conditions' => array('ordene_id' => $idOrden, 'documento_id' => 19)));
            if (empty($docpo))
            {
                $cantsinpo[$c] = $idOrden;
                $c++;
            }

        }
        //debug($cantsinpo);
        $povol = $this->Ordene->find('all', array('recursive' => -1, 'conditions' =>
                array('id' => $cantsinpo, 'volumenes !=' => 1)));
        $this->set(compact('povol'));
        //$this->set(compact('povol'));
        //debug($povol);
    }

    public function regulariza()
    {
        $sqlOrden = "SELECT MIN(transmital) as tran, lugar
                FROM planos
                GROUP BY lugar
                ORDER BY transmital";

        $planos = $this->Plano->query($sqlOrden);
        //debug($planos);
        $c = 1;
        foreach ($planos as $p)
        {
            $lugar = $p['planos']['lugar'];
            $this->Plano->updateAll(array('Plano.nuevolugar' => $c), array('Plano.lugar' =>
                    $lugar));
            $c++;
        }
        $this->redirect(array('controller' => 'Planos', 'action' => 'index'));
    }

    public function generales()
    {
        $this->layout = 'reportes';
        $dcPo = $this->Ordene->find('all', array('recursive' => -1, 'fields' => array('id',
                    'nombre')));
                    
        $monedas = $this->Ordene->find('all', array(
            'recursive'=>-1,
            'group'=>array('tipo_moneda'),
            'fields'=>array('Ordene.tipo_moneda','Ordene.tipo_moneda')
        ));
                
        $tagsCombo = array();
        $c=0;
                    
        $tagsPo = $this->OrdenesTag->find('all', array(
            'recursive'=>-1,
            'group'=>array('tag_id')
        ));
        
        $tagsCo = $this->CambiordenesTag->find('all', array(
            'recursive'=>-1,
            'group'=>array('tag_id')
        ));
                     
        foreach($tagsPo as $tpo)
        {
            $tagsCombo[$c]['tag'] = $tpo['OrdenesTag']['tag_id'];
            $c++; 
        }
         
        foreach($tagsCo as $tco)
        {
            $tagsCombo[$c]['tag'] = $tco['CambiordenesTag']['tag_id'];
            $c++; 
        }              
        //debug($tags);
        //debug($tagsPo);                         
        //debug($tagsCo);            
        $dcProveedorPo = $this->Ordene->find('all', array(
            'recursive' => -1,
            'fields' => array('id', 'proveedor'),
            'group' => 'proveedor'));

        $sqlProveedor = "SELECT proveedor
                        FROM contratos
                        GROUP BY proveedor";
        $dcProveedor = $this->Contrato->query($sqlProveedor);
        //debug($dcp);
        $dcContratos = $this->Contrato->find('all', array('recursive' => -1, 'fields' =>
                array('id', 'codigo')));

        $this->set(compact('dcPo', 'dcProveedor', 'dcContratos', 'dcProveedorPo', 'tagsCombo', 'monedas'));
    }

    public function indexordenes()
    {

        $ordenes = $this->Ordene->find('all', array('recursive' => 0));
        $this->set(compact('ordenes'));
    }

    public function detalle($id = null)
    {

        $orden = $this->Ordene->find('first', array('conditions' => array('Ordene.id' =>
                    $id)));

        /* $documentos = $this->DocumentosOrdene->find('all', array(
        'recursive' => 2,
        'conditions' => array('DocumentosOrdene.ordene_id' => $id)
        )); */
        //debug($orden);
        $sqlDoc = "SELECT doc.volumen, doc.original, doc.documento_id, documentos.nombre
                    FROM documentos_ordenes doc
                    LEFT JOIN documentos ON doc.documento_id = documentos.id
                    WHERE doc.ordene_id = $id";
        $documentos = $this->DocumentosOrdene->query($sqlDoc);
        //debug($documentos);
        $tagsOrdenes = $this->OrdenesTag->find('all', array('recursive' => 3,
                'conditions' => array('OrdenesTag.ordene_id' => $id)));
        //debug($tagsOrdenes);

        $sqlOrdenesTags = "SELECT ot.ordene_id, ot.tag_id, ot.monto, tags.descripcion1
                            FROM ordenes_tags ot
                            LEFT JOIN tags ON ot.tag_id = tags.id
                            WHERE ot.ordene_id = $id";

        $ordenesTags = $this->OrdenesTag->query($sqlOrdenesTags);

        //$sqlOrdenesCo = "SELECT *      FROM ";
        $cambiosO = $this->Cambiordene->find('all', array(
            'recursive' => -1,
            'conditions' => array('ordene_id' => $id),
            'order' => array('numero ASC')));

        //$tagsCambiorden = $this->CambiordenesTag->find('all', array('recursive' => 0,
        //        'conditions' => array('CambiordenesTag.Tag_id LIKE' => '%' . $tag . '%')));


        $tagsOrden = $this->OrdenesTag->find('all');
        //debug($cambiosO);
        //debug($ordenesTags);
        $this->set(compact('orden', 'tagsOrdenes', 'documentos', 'ordenesTags',
            'cambiosO', 'tagsCambiorden'));
        //debug($tagsCambiorden);
    }


    public function indexcontratos()
    {
        $contratos = $this->Contrato->find('all', array('recursive' => -1,
                //'fields'=>array('')
                ));
        $this->set(compact('contratos'));
    }

    public function detallecontrato($id = null)
    {

        $detalle = $this->Contrato->find('all', array(
            'recursive' => -1,
            'conditions' => array('Contrato.id' => $id),
            'orden' => array('numero ASC')));

        $this->set(compact('detalle', 'id'));
        //debug($detalle);

        //$detallecontrato = $this->Contrato->find('all', array('recursive' => 0));
        //$this->set(compact('detallecontrato'));
    }

    public function indexplanos()
    {
        $planos = $this->Plano->find('all', array('recursive' => 0));
        $this->set(compact('planos'));
    }

    public function busproveedor()
    {
        //debug($this->request->data);
        //exit;
        $proveedor = $this->request->data['Publicos']['proveedor'];
        $datosContrato = $this->Contrato->find('all', array('recursive' => -1,
                'conditions' => array('Contrato.proveedor LIKE' => '%' . $proveedor . "%")));
        $this->set(compact('datosContrato'));
        // debug($datosContrato);
    }


    public function buscapo()
    {
        //debug($this->request->data);
        //exit;

        $po = $this->request->data['Publicos']['po'];
        $datosPo = $this->Ordene->find('all', array('recursive' => -1, 'conditions' =>
                array('Ordene.nombre LIKE' => '%' . $po . '%')));
        $this->set(compact('datosPo'));
        //debug($datosPo);
    }

    public function buscontrato()
    {
        //debug($this->request->data);
        //exit;

        $contrato = $this->request->data['Publicos']['codigo'];
        $datosCont = $this->Contrato->find('all', array('recursive' => -1, 'conditions' =>
                array('Contrato.codigo LIKE' => '%' . $contrato . '%')));

        $this->set(compact('datosCont'));
        //debug($datosCont);
    }

    public function buscotags()
    {
        //debug($this->request->data);
        //exit;
        
        $tag = $this->request->data['Publicos']['tags'];
        
        $infoTag = $this->Tag->find('all', array(
            'recursive'=>-1,
            
        ));                
        
        //debug($infoTag);

        //muestra los tags de los Ordenes
        $tagsOrdenes = $this->OrdenesTag->find('all', array('recursive' => 1,
                'conditions' => array('OrdenesTag.Tag_id LIKE' => '%' . $tag . '%')));

        //muestra los tags de los Cambios de Orden
        $tagsCambioOrden = $this->CambiordenesTag->find('all', array(
            'recursive' => 0,
            'conditions' => array('CambiordenesTag.Tag_id LIKE' => '%' . $tag . '%')                        
        ));

        //muestra los tags de los Planos de Orden
        $tagsPlanos = $this->PlanosTag->find('all', array('recursive' => 0, 'conditions' =>
                array('PlanosTag.Tag_id LIKE' => '%' . $tag . '%')));

        $ordenes = $this->Ordene->find('all', array('recursive' => 0));
        //$this->set(compact('ordenes'));
                
        /*debug($tagsOrdenes);
        debug($tagsCambioOrden);
        debug($tagsPlanos);*/

        $this->set(compact('ordenes', 'tagsOrdenes', 'tagsCambioOrden', 'tagsPlanos', 'tag'));
    }

    public function proveedorPo()
    {
        //debug($this->request->data);
        //exit;
        $proveedor = $this->request->data['Publicos']['proveedor'];
        //$datosPo = "SELECT *
        //            FROM `ordenes`
        //        GROUP BY proveedor";
        $datosPo = $this->Ordene->find('all', array('recursive' => -1, 'conditions' =>
                array('Ordene.proveedor LIKE' => '%' . $proveedor . "%")));

        $this->set(compact('datosPo'));
        //debug($datosPo);
    }

    public function detalletag()
    {
        //debug($this->request->data);
        //exit;
        $tag = $this->request->data['Publicos']['tags'];

        //muestra los tags de los Ordenes
        $tagsOrdenes = $this->OrdenesTag->find('all', array('recursive' => 0,
                'conditions' => array('OrdenesTag.Tag_id LIKE' => '%' . $tag . '%')));

        //muestra los tags de los Cambios de Orden
        $tagsCambioOrden = $this->CambiordenesTag->find('all', array('recursive' => 0,
                'conditions' => array('CambiordenesTag.Tag_id LIKE' => '%' . $tag . '%')));

        //muestra los tags de los Planos de Orden
        $tagsPlanos = $this->PlanosTag->find('all', array('recursive' => 0, 'conditions' =>
                array('PlanosTag.Tag_id LIKE' => '%' . $tag . '%')));

        // debug($tagsOrdenes);
        // debug($tagsCambioOrden);
        // debug($tagsPlanos);

        $this->set(compact('tagsOrdenes', 'tagsCambioOrden', 'tagsPlanos'));
    }

    public function montomayor()
    {
        
        $datosCont = $this->Contrato->find('all', array(
            'recursive' => -1,
            'conditions' => array('Contrato.codigo LIKE' => '%' . $contrato . '%'),
            'order'=>array('Contrato.monto3 DESC'),
            'limit'=>20
            ));

        $this->set(compact('datosCont'));
        //debug($datosCont);
    }

    public function montomayorpo($id = null)
    {
        if(!empty($this->request->data)){
            
            $dato = $this->request->data['selectReq'];
            $datosPo = $this->Ordene->find('all', array(
        'conditions'=>array('Ordene.tipo_moneda LIKE'=>'%'.$dato.'%'),
        'recursive' => -1,
        'order'=>array('Ordene.monto DESC'),
        'limit'=>20
         ));
         $datosPorev = $this->Ordene->find('all', array(
         'conditions'=>array('Ordene.tipo_moneda LIKE'=>'%'.$dato.'%', 
         'OR'=>array('Ordene.monto !='=>0.00, 'Ordene.monto !='=>null)),
        'recursive' => -1,
        'order'=>array('Ordene.monto ASC'),
        'limit'=>20
         ));
         $datosCeros = $this->Ordene->find('all', array(
         'conditions'=>array('Ordene.tipo_moneda LIKE'=>'%'.$dato.'%', 
         'OR'=>array('Ordene.monto'=>0.00, 'Ordene.monto'=>null)),
        'recursive' => -1,
        'order'=>array('Ordene.fecha DESC'),
        'limit'=>20
         ));
         $this->set(compact('datosPo','datosPorev', 'datosCeros'));
         $ordenes = $this->Ordene->find('all', array('recursive' => 0));
        $this->set(compact('ordenes'));
        }
        
    }
    public function co($idOrden = null)
    {

        $cambiosO = $this->Cambiordene->find('all', array(
            'recursive' => -1,
            'conditions' => array('ordene_id' => $idOrden),
            'orden' => array('numero ASC')));
        $this->set(compact('cambiosO', 'idOrden'));
    }

    public function ajaxmuestratagsco($id = null)
    {
        $this->layout='ajax';
        $tagsOrden = $this->CambiordenesTag->find('all', array(
            'recursive'=>-1,
            'conditions'=>array('CambiordenesTag.cambiordene_id'=>$id)
        ));
        $this->set(compact('tagsOrden'));
        //debug($tagsOrden);
    }
    
    public function ajaxcambiocontrato($codContrato=null)
    {
        $this->layout='ajax';
        $cambiosContrato = $this->Cambiocontrato->find('all', array(
            'recursive'=>-1,
            'conditions'=>array('Cambiocontrato.codcontrato'=>$codContrato)
        ));       
        //debug($cambiosContrato);
        $this->set(compact('cambiosContrato'));
    }
    
    

}
